/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Utils;

import java.io.Serializable;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author aleX
 */
public class dbManager implements Serializable{    
    // transient == non viene serializzato
    private transient Connection con;
public dbManager(String dburl) throws SQLException {
    try {
        Class.forName("com.mysql.jdbc.Driver", true,
        getClass().getClassLoader());
    } catch(Exception e) {
    throw new RuntimeException(e.toString(), e);
    }
Connection con = DriverManager.getConnection(dburl,"root","root");
this.con = con;
}

    public static void shutdown(){
//        try{
//            DriverManager.getConnection("jdbc:mysql://localhost:3306/mercatodb", "root", "root");
//        }catch(SQLException ex){
//            Logger.getLogger(dbManager.class.getName()).info(ex.getMessage());
//        }
    }
    
    //AUTENTICA UTENTE
    public User authenticate(String username, String password, String roles) throws SQLException{
     //Connection con= DriverManager.getConnection("jdbc:mysql://localhost:3306/mercatodb");
     //Connection con = dbManager.getConnection();   
 
        PreparedStatement stm = con.prepareStatement("SELECT username,roles,id_user FROM utente WHERE username = ? AND password = ?");
try {
    stm.setString(1, username);
    stm.setString(2, password);
    ResultSet rs = stm.executeQuery();
        try {
            if (rs.next()) {
                User user = new User();
             user.setUsername(rs.getString("username"));
             user.setRoles(rs.getString("roles"));
             user.setId_User(rs.getInt("id_user"));
          

             return user;
            } else {
                return null;
            }
        } finally {
            rs.close();
        }
} finally { 
    stm.close();
}}



public List<Product> getProducts(String selezione) throws SQLException {
List<Product> products = new ArrayList<Product>();

if(selezione.equals("agrumi")) selezione="agrumi";
 
 if(selezione.equals("frutta"))selezione="frutta";          
           
 if(selezione.equals("formaggi"))selezione="formaggi";
          
 if(selezione.equals("ortaggi"))selezione="ortaggi";

    PreparedStatement stm = con.prepareStatement("SELECT p.id_products,p.product,p.price,p.quantity,u.username,p.category,p.um FROM products AS p INNER JOIN utente AS u ON p.id_user=u.id_user WHERE p.category=? ");



try {
    stm.setString(1, selezione);
ResultSet rs = stm.executeQuery();
try {
while(rs.next()) {
Product p = new Product();
p.setId_Product(rs.getInt("id_products"));
p.setProduct(rs.getString("product"));
p.setPrice(rs.getFloat("price"));
p.setQuantity(rs.getInt("quantity"));
p.setUsername(rs.getString("username"));
p.setCategory(rs.getString("category"));
p.setUm(rs.getString("um"));
System.out.println(p.getProduct());
products.add(p);
}} finally {
rs.close();
}
} finally {
stm.close();
}
return products;
}

public List<Product> getProductsSeller(String username) throws SQLException {
List<Product> products = new ArrayList<Product>();

PreparedStatement stm = con.prepareStatement("SELECT p.id_products,p.product,p.price,p.quantity,p.category,p.um FROM products AS p INNER JOIN utente AS u ON p.id_user=u.id_user WHERE u.username=? ");


try {
    stm.setString(1, username);
ResultSet rs = stm.executeQuery();
try {
while(rs.next()) {
Product p = new Product();
p.setProduct(rs.getString("product"));
p.setPrice(rs.getFloat("price"));
p.setQuantity(rs.getInt("quantity"));
p.setCategory(rs.getString("category"));
p.setUm(rs.getString("um"));
p.setId_Product(rs.getInt("id_products"));

products.add(p);
}} finally {
rs.close();
}
} finally {
stm.close();
}
return products;
}

public List<Product> upProductsSeller(String username,String prod,int quant,String categ,String um,Float price,Timestamp t) throws SQLException {
        int id=0;
    List<Product> products = new ArrayList<Product>();
PreparedStatement stm = con.prepareStatement("SELECT id_user FROM utente WHERE username=?");

try {
    stm.setString(1, username);
    
 ResultSet rs = stm.executeQuery();


try{
if (rs.next()) {
            User user = new User();
            user.setId_User(rs.getInt("id_user"));
            id = user.getId_User();
               
          }
} finally {
     rs.close();
        }
} finally { 
    stm.close();
   }

PreparedStatement stm2 = con.prepareStatement("INSERT INTO products(id_user,product,quantity,category,um,price,date_stamp) VALUES (?,?,?,?,?,?,?)");
 try{
    stm2.setInt(1,id);

    stm2.setString(2, prod);
    stm2.setInt(3, quant);
    stm2.setString(4, categ);
    stm2.setString(5, um);
    stm2.setFloat(6, price);
    stm2.setTimestamp(7, t);
  
    stm2.executeUpdate();

} finally {
stm.close();
}
return products;
}




public List<Product> getConfirm(int id_P) throws SQLException {
List<Product> products = new ArrayList<Product>();



PreparedStatement stm = con.prepareStatement("SELECT p.id_products,p.product,p.price,p.quantity,u.username,p.category,p.um,p.date_stamp FROM products AS p INNER JOIN utente AS u ON p.id_user=u.id_user WHERE p.id_products=? ");


try {
stm.setInt(1, id_P);
ResultSet rs = stm.executeQuery();
try {
while(rs.next()) {
Product p = new Product();
p.setId_Product(rs.getInt("id_products"));
p.setProduct(rs.getString("product"));
p.setPrice(rs.getFloat("price"));
p.setQuantity(rs.getInt("quantity"));
p.setUsername(rs.getString("username"));
p.setCategory(rs.getString("category"));
p.setUm(rs.getString("um"));
p.setDate_stamp(rs.getTimestamp("date_stamp"));

products.add(p);
}} finally {
rs.close();
}
} finally {
stm.close();
}
return products;
}

public void getBuy(int id_P) throws SQLException {
//List<Product> products = new ArrayList<Product>();
PreparedStatement stm = con.prepareStatement("DELETE FROM products WHERE id_products=? ");
try {
    stm.setInt(1, id_P);
    stm.executeUpdate();
}finally {
stm.close();
}
}


public List<Product> upAcquisto(int idP,String idU,int idBuyer,int quant,String url,float price) throws SQLException {
   int idUSER=0;
    PreparedStatement stm = con.prepareStatement("SELECT date_stamp FROM products WHERE id_products=?");

Timestamp data = null;

    try {
    stm.setInt(1, idP);
    
 ResultSet rs = stm.executeQuery();
try{
if (rs.next()) {
            Product p = new Product();
            p.setDate_stamp(rs.getTimestamp("date_stamp"));
        
            data= p.getDate_stamp();
           
            
          }
} finally {
     rs.close();
        }
} finally { 
    stm.close();
   }
//2
    PreparedStatement stm2 = con.prepareStatement("SELECT id_user FROM utente WHERE username=?");

try {
    stm2.setString(1, idU);
    
 ResultSet rs = stm2.executeQuery();


try{
if (rs.next()) {
            User user = new User();
            user.setId_User(rs.getInt("id_user"));
            idUSER = user.getId_User();
                
          }
} finally {
     rs.close();
        }
} finally { 
    stm2.close();
   }
    
    List<Product> products = new ArrayList<Product>();

PreparedStatement stm3 = con.prepareStatement("INSERT INTO sell(datestamp,id_product,id_seller,id_buyer,quantity,price,url_pdf) VALUES (?,?,?,?,?,?,?)");
 try{
    stm3.setTimestamp(1,data);
   
    
    stm3.setInt(2, idP);
    stm3.setInt(3, idUSER);
    stm3.setInt(4, idBuyer);
    stm3.setInt(5, quant);
    stm3.setFloat(6, price);
    stm3.setString(7, url);
    stm3.executeUpdate();

} finally {
stm3.close();
}
return products;
}

public List<Sell> urlPDF(int idBuy) throws SQLException{
    List<Sell> sell= new ArrayList<Sell>();
    PreparedStatement stm = con.prepareStatement("SELECT url_pdf FROM sell WHERE id_buyer=? ");

try {
    stm.setInt(1, idBuy);
    ResultSet rs = stm.executeQuery();

try{
while (rs.next()) {
    Sell s=new Sell();        
    s.setUrl(rs.getString("url_pdf"));
   
    sell.add(s);
     }
} finally {
     rs.close();
        }
} finally { 
    stm.close();
   }
return sell;
}



}
